Deleting Records Using DELETE in MySQL

Course: MySQL Training

Topic: Deleting Records Using DELETE

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Deleting Records Using DELETE

In MySQL, the DELETE statement is used to remove existing records from a database table. It is a Data Manipulation Language (DML) statement that allows users to delete one record, multiple records, or all records from a table, depending on the condition specified.

Deleting records is an important database operation. It helps remove outdated information, duplicate entries, invalid records, and data that is no longer required. However, the DELETE statement must be used carefully because deleted records may not be recoverable unless a transaction can be rolled back or a backup is available.

For example, a company database may contain employee records for people who have left the organization. If those records are no longer required in a particular table, a database administrator may use DELETE to remove the relevant rows.

Basic Syntax

DELETE FROM table_name
WHERE condition;

• DELETE specifies that records should be removed.
• FROM identifies the table from which records will be deleted.
• table_name is the name of the target table.
• WHERE specifies which records should be deleted.
• condition defines the rows that match the deletion criteria.

Important: If the WHERE clause is omitted, a DELETE statement removes all rows from the specified table. Always verify the target table and condition before executing a deletion.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the purpose of the DELETE statement in MySQL.
• Identify the difference between DELETE, TRUNCATE, and DROP.
• Delete a single record using a WHERE condition.
• Delete multiple records using comparison and logical operators.
• Use DELETE with IN, BETWEEN, LIKE, and subqueries.
• Preview records before deleting them.
• Use transactions to make certain deletions reversible before committing.
• Understand foreign-key constraints and referential integrity.
• Recognize common DELETE errors and troubleshoot them.
• Apply safe deletion practices in real-world database projects.

3. Understanding the DELETE Statement

The DELETE statement removes rows from an existing table while keeping the table structure intact. The table's columns, indexes, and other structural definitions generally remain in place.

Example Table: employees

Assume a database named company_db contains an employees table with the following records.

The examples in this topic use this table to demonstrate different deletion scenarios. Run them in a practice database rather than on production data.

4. Preparing the Database and Table

If you need a practice table, you can create one using the following statements. If the table already exists and contains important data, do not run the setup statements without understanding their effects.

CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    city VARCHAR(50)
);

Insert sample data only when the table does not already contain the same employee IDs.

INSERT INTO employees
(employee_id, employee_name, department, salary, city)
VALUES
(101, 'Amit Sharma', 'IT', 55000, 'Mumbai'),
(102, 'Priya Verma', 'HR', 45000, 'Pune'),
(103, 'Rahul Singh', 'IT', 60000, 'Delhi'),
(104, 'Neha Patel', 'Finance', 50000, 'Mumbai'),
(105, 'Vikram Joshi', 'Sales', 40000, 'Nashik');

If these IDs already exist, the INSERT statement may fail because employee_id is a primary key. Check the existing data before inserting sample rows.

5. Deleting a Single Record Using DELETE

To delete a specific record, use a WHERE clause that identifies the intended row. A primary key is usually a good choice because it uniquely identifies a record.

Example: Delete One Employee

DELETE FROM employees
WHERE employee_id = 105;

This statement deletes the employee whose ID is 105. Other employee records remain unchanged.

Verify the Result

SELECT *
FROM employees
WHERE employee_id = 105;

If the deletion succeeded and no matching row was inserted again, the query returns no records.

Best Practice

Before deleting, inspect the target row with SELECT:

SELECT *
FROM employees
WHERE employee_id = 105;

Confirm that the returned record is the one you intend to remove, and then execute the DELETE statement.

6. Deleting Multiple Records Using WHERE

The DELETE statement can remove several records when multiple rows satisfy the same condition. Every row matching the condition may be deleted.

Example: Delete Employees in the HR Department

DELETE FROM employees
WHERE department = 'HR';

This statement removes all records whose department value is HR. It does not delete records from other departments.

Example: Delete Employees in Mumbai

DELETE FROM employees
WHERE city = 'Mumbai';

Every employee whose city is Mumbai matches the condition and is deleted.

Preview Before Deleting

SELECT *
FROM employees
WHERE city = 'Mumbai';

Use the SELECT query first to confirm the number and identity of the rows that match the intended condition.

7. Deleting Records with Comparison Operators

Comparison operators allow you to delete records based on numeric, text, or date-related conditions.

Example 1: Delete Employees with Salary Below 45000

DELETE FROM employees
WHERE salary < 45000;

Example 2: Delete Employees Not in IT

DELETE FROM employees
WHERE department <> 'IT';

Use inequality conditions carefully. They can match many rows, including records with different departments or cities than you expected.

8. Deleting Records with AND and OR

Logical operators help combine multiple conditions in one DELETE statement.

Using AND

AND requires all specified conditions to be true.

DELETE FROM employees
WHERE department = 'IT'
AND salary < 60000;

This deletes employees who belong to IT and have a salary below 60000.

Using OR

OR matches a row when at least one of the specified conditions is true.

DELETE FROM employees
WHERE city = 'Pune'
OR city = 'Nashik';

This deletes employees located in Pune or Nashik.

Using Parentheses to Control Conditions

DELETE FROM employees
WHERE department = 'IT'
AND (city = 'Mumbai' OR city = 'Delhi');

Parentheses make the intended logic explicit. Without careful grouping, a condition containing AND and OR may match a different set of rows than expected.

9. Deleting Records Using IN and NOT IN

The IN operator matches a value against a list of specified values. It can make a DELETE statement easier to read than a series of OR conditions.

Using IN

DELETE FROM employees
WHERE city IN ('Mumbai', 'Pune', 'Nashik');

This removes employees whose city is any of the listed values.

Using NOT IN

DELETE FROM employees
WHERE department NOT IN ('IT', 'HR');

This removes rows whose department is neither IT nor HR. If department is NULL, the condition does not evaluate to true, so those rows are not selected by this condition.

Tip: For deletion involving a subquery, be especially careful when NULL values are possible. NOT IN can produce surprising results when its list or subquery contains NULL.

10. Deleting Records Using BETWEEN

The BETWEEN operator matches values within an inclusive range. Both endpoints are included in the comparison.

Example: Delete Employees with Salaries Between 40000 and 50000

DELETE FROM employees
WHERE salary BETWEEN 40000 AND 50000;

This matches employees whose salary is at least 40000 and at most 50000.

Example: Delete Records Within a Date Range

Assume another table named orders contains a date column named order_date.

DELETE FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

If order_date is a DATE column, this includes dates from January 1 through January 31, 2026. If it is a DATETIME or TIMESTAMP column, a condition ending at midnight on January 31 may exclude records later that day. A half-open date range is usually safer for datetime data:

DELETE FROM orders
WHERE order_date >= '2026-01-01'
AND order_date < '2026-02-01';

11. Deleting Records Using LIKE

The LIKE operator matches text patterns. The percent sign (%) represents zero or more characters, while the underscore (_) represents exactly one character.

Example 1: Delete Employees Whose Names Start with A

DELETE FROM employees
WHERE employee_name LIKE 'A%';

Example 2: Delete Employees Whose Names End with Sharma

DELETE FROM employees
WHERE employee_name LIKE '%Sharma';

Example 3: Delete Employees Whose Names Contain the Word Verma

DELETE FROM employees
WHERE employee_name LIKE '%Verma%';

Text matching can depend on the column's collation, including its case-sensitivity rules. Always preview the matching rows before running a pattern-based deletion.

12. Deleting Records Where a Column Is NULL

NULL represents a missing or unknown value. In SQL, it should be checked with IS NULL or IS NOT NULL, rather than using the equals operator.

Delete Records with a Missing City

DELETE FROM employees
WHERE city IS NULL;

Delete Records Where City Is Present

DELETE FROM employees
WHERE city IS NOT NULL;

The second example can remove a large number of rows. Verify the matching data before executing it.

13. Deleting Records Using a Subquery

A subquery is a query nested inside another SQL statement. It can identify records for deletion based on data stored in another table.

Assume the database has an inactive_employees table containing employee IDs that should be removed from the main employees table.

Example: Delete Employees Listed as Inactive

DELETE FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM inactive_employees
);

Depending on the table definitions and MySQL restrictions, deleting from a table while selecting from the same table in a subquery may require a derived-table approach. Using a separate reference table, as above, avoids that specific same-table pattern.

Preview the Target Records

SELECT *
FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM inactive_employees
);

Make sure the subquery returns the intended IDs before deleting records.

14. Deleting Records with ORDER BY and LIMIT

MySQL supports ORDER BY and LIMIT in single-table DELETE statements. LIMIT restricts the maximum number of rows deleted, and ORDER BY can help define which matching rows are targeted first.

Example: Delete One Lowest-Salary Employee

DELETE FROM employees
ORDER BY salary ASC, employee_id ASC
LIMIT 1;

This deletes one row with the lowest salary, using employee_id as a secondary ordering rule to make the selection more deterministic when salaries are equal.

Example: Delete Up to Five Old Log Records

DELETE FROM application_logs
WHERE log_level = 'DEBUG'
ORDER BY created_at ASC, log_id ASC
LIMIT 5;

This removes up to five matching DEBUG log records, starting with the oldest according to the specified ordering.

Important: ORDER BY and LIMIT should be used only when their behavior is supported by the specific DELETE form and your MySQL version. Review the query plan and matching rows first, especially for large tables.

15. Deleting Records Without a WHERE Clause

A DELETE statement without a WHERE clause removes all rows from the target table.

DELETE FROM employees;

This statement deletes every row in the employees table. The table definition remains, but its rows are removed. This operation should be performed only when clearing the entire table is intentional.

Safer Alternative: Preview the Row Count

SELECT COUNT(*) AS total_records
FROM employees;

If your goal is to clear the table, confirm that you have selected the correct database and table and that a backup or recovery plan is available. Do not use a no-WHERE DELETE as a substitute for removing a few specific records.

16. DELETE vs TRUNCATE vs DROP

DELETE, TRUNCATE, and DROP have different purposes and effects. Choosing the correct statement helps prevent accidental data loss.

DELETE Example

DELETE FROM employees
WHERE employee_id = 101;

TRUNCATE Example

TRUNCATE TABLE employees;

DROP Example

DROP TABLE employees;

TRUNCATE and DROP can have significant effects on a database. Foreign-key relationships, storage-engine behavior, permissions, and MySQL version may affect whether a statement can be executed. Use them only when you understand the consequences.

17. Using Transactions to Delete Records Safely

A transaction allows you to group database operations so that changes can be committed or rolled back under supported conditions. For tables using a transactional storage engine such as InnoDB, you can inspect a deletion before making it permanent.

MySQL commonly uses autocommit mode by default. When autocommit is enabled, a DELETE statement executed outside an explicit transaction is normally committed automatically if it succeeds. Therefore, use START TRANSACTION before the DELETE if you may need to undo it.

Example: Delete a Record and Roll Back the Change

START TRANSACTION;

DELETE FROM employees
WHERE employee_id = 105;

SELECT *
FROM employees;

ROLLBACK;

The ROLLBACK statement cancels the deletion within the current transaction, provided the table uses a transactional storage engine and the transaction has not already been committed. After the rollback, the deleted row should be present again.

Example: Delete a Record and Commit the Change

START TRANSACTION;

DELETE FROM employees
WHERE employee_id = 105;

SELECT *
FROM employees
WHERE employee_id = 105;

COMMIT;

COMMIT makes the transaction's changes permanent. In this example, the employee is deleted after the transaction is committed.

Important: If you inspect the result and discover that the wrong records were deleted, execute ROLLBACK before COMMIT. Once the transaction has been committed, ROLLBACK cannot undo that committed change. Recovery may require a backup or another recovery mechanism.

18. Using SAVEPOINT During Deletion

A SAVEPOINT marks a position inside a transaction. It allows you to roll back part of the work without necessarily cancelling every change made since the transaction started.

Example

START TRANSACTION;

DELETE FROM employees
WHERE employee_id = 105;

SAVEPOINT after_first_delete;

DELETE FROM employees
WHERE employee_id = 104;

ROLLBACK TO SAVEPOINT after_first_delete;

COMMIT;

In this example, the deletion of employee 104 is rolled back to the savepoint, while the earlier deletion of employee 105 remains part of the transaction and is committed.

Use savepoints carefully and remember that transaction behavior depends on the storage engine and the statements executed. SAVEPOINT does not replace backups or careful verification.

19. Checking the Number of Deleted Records

After a DELETE statement, MySQL reports how many rows were affected. You can also use the ROW_COUNT() function to retrieve the number of rows affected by the immediately preceding statement.

Example

DELETE FROM employees
WHERE department = 'Sales';

SELECT ROW_COUNT() AS deleted_rows;

The result of ROW_COUNT() indicates the number of rows affected by the preceding DELETE statement. Execute the function immediately after the statement whose result you want to inspect, because another statement may change the reported count.

Verify Remaining Records

SELECT COUNT(*) AS remaining_employees
FROM employees;

This query counts the rows remaining in the employees table. It does not show how many rows were deleted by itself, so use it together with the affected-row count when appropriate.

20. Deleting Records with Foreign-Key Relationships

In a relational database, one table may reference another table through a foreign key. For example, an orders table may contain a customer_id column that references the primary key of a customers table.

Foreign-key constraints help maintain referential integrity. They may prevent a parent record from being deleted while related child records still reference it.

Example: Customers and Orders

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

Suppose customer 1 has orders in the orders table. Attempting to delete that customer may fail if the foreign key uses the default restrictive behavior and matching child records still exist.

DELETE FROM customers
WHERE customer_id = 1;

If related orders exist and the foreign key does not permit the deletion, MySQL rejects the statement to protect referential integrity.

Possible Ways to Handle Related Records

• Delete the related child records first if business rules permit it.
• Retain the customer record and mark it inactive instead of deleting it.
• Use an appropriate foreign-key action such as ON DELETE CASCADE only when automatic deletion of related records is intended.
• Review the data model and business requirements before changing foreign-key rules.

Warning: ON DELETE CASCADE can delete related records automatically. Never enable or rely on cascading deletion without understanding which child records will be affected.

21. Using DELETE with JOIN

MySQL supports multi-table DELETE syntax, which can delete rows from one or more tables based on relationships between tables. This is an advanced operation and should be used only after carefully checking the join conditions.

Example: Delete Orders for Inactive Customers

Assume a customers table contains an is_active column, where 0 represents an inactive customer.

DELETE o
FROM orders AS o
JOIN customers AS c
    ON o.customer_id = c.customer_id
WHERE c.is_active = 0;

This statement deletes matching rows from the orders table, identified by the alias o, for customers whose is_active value is 0. It does not delete customer records because only the orders alias is specified as the deletion target.

Preview the Matching Orders First

SELECT o.*
FROM orders AS o
JOIN customers AS c
    ON o.customer_id = c.customer_id
WHERE c.is_active = 0;

Review the returned orders before running the DELETE. If the query joins the wrong columns or matches too many rows, the deletion may remove unintended data.

22. Common Errors When Using DELETE

23. Best Practices for Safe DELETE Operations

• Use WHERE carefully: Specify the exact criteria for the records you intend to delete.
• Prefer primary keys: A unique primary key usually makes the target record unambiguous.
• Preview the records: Run SELECT with the same condition before running DELETE.
• Check the active database: Confirm that the correct schema and table are selected.
• Use transactions where supported: Start a transaction when you need the opportunity to inspect changes and roll them back.
• Back up important data: Maintain a tested backup and recovery plan for important databases.
• Consider foreign keys: Understand how parent and child records are related before deleting data.
• Use LIMIT cautiously: If deleting a limited number of records, use a suitable ORDER BY when you need a predictable selection.
• Be careful with NULL: Use IS NULL and IS NOT NULL instead of equality comparisons with NULL.
• Avoid unnecessary full-table deletions: A DELETE statement without WHERE removes every row from the specified table.
• Use appropriate privileges: Applications and database users should have only the permissions required for their tasks.
• Test in development: Validate deletion logic in a test environment before applying it to production data.

24. Practical Exercises

Use a practice database to complete the following exercises. Review the matching rows with SELECT before executing each DELETE statement.

1. Delete the employee whose employee_id is 103.
2. Delete employees who belong to the Finance department.
3. Delete employees whose salary is below 45000.
4. Delete employees who live in either Mumbai or Pune.
5. Delete employees whose names start with the letter N.
6. Delete employees whose city is NULL.
7. Delete up to two employees with the lowest salaries, using employee_id as a tie-breaker.
8. Start a transaction, delete one test record, verify the result, and roll back the deletion.
9. Use ROW_COUNT() to check how many rows were deleted by a test DELETE statement.
10. Create customers and orders tables, then investigate how a foreign key affects attempts to delete a customer with existing orders.

Sample Solutions

-- 1. Delete a specific employee
DELETE FROM employees
WHERE employee_id = 103;

-- 2. Delete employees in Finance
DELETE FROM employees
WHERE department = 'Finance';

-- 3. Delete employees earning less than 45000
DELETE FROM employees
WHERE salary < 45000;

-- 4. Delete employees in Mumbai or Pune
DELETE FROM employees
WHERE city IN ('Mumbai', 'Pune');

-- 5. Delete employees whose names start with N
DELETE FROM employees
WHERE employee_name LIKE 'N%';

-- 6. Delete employees with a missing city
DELETE FROM employees
WHERE city IS NULL;

-- 7. Delete up to two employees with the lowest salaries
DELETE FROM employees
ORDER BY salary ASC, employee_id ASC
LIMIT 2;

-- 8. Test a deletion and undo it
START TRANSACTION;

DELETE FROM employees
WHERE employee_id = 105;

SELECT *
FROM employees
WHERE employee_id = 105;

ROLLBACK;

-- 9. Check the affected-row count
DELETE FROM employees
WHERE employee_id = 105;

SELECT ROW_COUNT() AS deleted_rows;

These statements modify data. Run the solutions individually and only against test data. Do not execute every example on an important database without checking its effect.

25. Mini Project: Employee Record Cleanup

Project Objective

Create a safe cleanup process for a practice employee database. The goal is to remove selected test records while preserving the remaining employee data.

Project Requirements

• Create an employees table with an employee ID, name, department, salary, and city.
• Insert sample employee records for testing.
• Identify employees that match a cleanup rule, such as a specific test department.
• Preview matching records using SELECT.
• Delete the selected rows using a precise WHERE condition.
• Check the affected-row count.
• Verify that unrelated employee records remain in the table.
• Repeat the process inside a transaction and use ROLLBACK to test recovery.
• Document the condition used, the number of rows affected, and the verification results.

Example Workflow

-- Step 1: Preview records targeted for cleanup
SELECT *
FROM employees
WHERE department = 'Test';

-- Step 2: Start a transaction
START TRANSACTION;

-- Step 3: Delete only the intended test records
DELETE FROM employees
WHERE department = 'Test';

-- Step 4: Check the affected-row count
SELECT ROW_COUNT() AS deleted_rows;

-- Step 5: Verify remaining records
SELECT *
FROM employees
ORDER BY employee_id;

-- Step 6: Choose one action:
-- Use ROLLBACK to undo the deletion
-- Use COMMIT to make the deletion permanent

ROLLBACK;

For this practice workflow, ROLLBACK is used so the test deletion can be undone. In a real cleanup operation, commit only after confirming that the correct rows were targeted and the intended changes are acceptable.

26. MySQL DELETE Interview Questions and Answers

Q1. What is the DELETE statement in MySQL?

Answer: DELETE is a DML statement used to remove rows from a table. It can remove selected records with WHERE or all records when WHERE is omitted.

Q2. What is the basic syntax of DELETE?

DELETE FROM table_name
WHERE condition;

Q3. What happens if WHERE is omitted?

Answer: All rows in the target table are deleted. The table structure remains intact.

Q4. Does DELETE remove the table structure?

Answer: No. DELETE removes rows but retains the table definition, indexes, and other structural objects, subject to the effects of triggers and related database behavior.

Q5. What is the difference between DELETE and TRUNCATE?

Answer: DELETE can remove selected rows using WHERE. TRUNCATE TABLE removes all rows and does not support WHERE. TRUNCATE also has different transaction and implicit-commit behavior.

Q6. Can deleted records be recovered?

Answer: If a deletion is inside an active transaction on a transactional table, ROLLBACK may undo it before COMMIT. After a committed deletion, recovery generally requires a backup, point-in-time recovery, or another recovery mechanism.

Q7. How can you delete multiple records?

Answer: Use a WHERE condition that matches all intended rows, such as DELETE FROM employees WHERE department = 'HR';.

Q8. Can DELETE be used with LIMIT?

Answer: Yes. MySQL supports LIMIT in single-table DELETE statements. ORDER BY can be used to help determine which matching rows are deleted first.

Q9. How do you delete records where a column is NULL?

Answer: Use IS NULL, for example DELETE FROM employees WHERE city IS NULL;.

Q10. What is a foreign-key constraint error?

Answer: It can occur when a row is referenced by rows in another table and the foreign-key rule does not allow the deletion.

Q11. How do you verify a DELETE operation?

Answer: Check the affected-row count, run SELECT using the same condition, and inspect the remaining table data.

Q12. What is the purpose of START TRANSACTION?

Answer: It starts an explicit transaction so supported changes can be committed with COMMIT or undone with ROLLBACK.

Q13. What is the difference between DELETE and DROP?

Answer: DELETE removes rows from a table. DROP TABLE removes the table definition and its data.

Q14. What should you do before deleting production records?

Answer: Verify the database and table, preview the exact matching rows, check relationships and constraints, confirm backup and recovery arrangements, and follow the organization's approval process.

27. Quick Revision Notes

• DELETE FROM table_name WHERE condition; removes rows matching a condition.
• A DELETE statement without WHERE removes all rows in the target table.
• Use SELECT with the same WHERE condition to preview records before deletion.
• Use a primary key when you need to identify a particular record precisely.
• Use AND, OR, IN, BETWEEN, LIKE, and comparison operators to define deletion criteria.
• Use IS NULL or IS NOT NULL to check missing values.
• Use ORDER BY and LIMIT in supported single-table DELETE statements when controlled deletion is required.
• Use START TRANSACTION, ROLLBACK, and COMMIT appropriately for transactional tables.
• Foreign-key constraints may prevent a deletion or trigger configured cascading behavior.
• DELETE removes rows; TRUNCATE clears a table; DROP removes the table itself.
• Check ROW_COUNT() immediately after the DELETE statement when you need its affected-row count.
• Always use backups, verification, and suitable permissions for important data.

28. Conclusion

The DELETE statement is an essential MySQL command for removing unwanted records from database tables. By using precise WHERE conditions, previewing rows with SELECT, checking affected-row counts, understanding foreign-key relationships, and using transactions where supported, developers can perform deletion operations more safely.

For practical database work, remember that a successful DELETE statement does not necessarily mean the correct records were deleted. Always verify the target rows and understand the recovery options before changing important data.

Continue Learning: Explore more database concepts and hands-on SQL practice through the MySQL Training Course.

Register for a Demo: Visit JustAcademy Course Demo Registration to learn more about the training.